from typing import List, Dict, Any
from .db import get_db
import logging

# 配置日志
logger = logging.getLogger(__name__)

# 考勤统计相关函数
def get_multidimensional_workhours_statistics(
        start_date: str,
        end_date: str,
        dimensions: List[str]
) -> List[Dict[str, Any]]:
    """
    多维度工时统计功能

    参数:
    - start_date: 开始日期
    - end_date: 结束日期
    - dimensions: 统计维度列表，可包含 'affiliation', 'dept1', 'dept2', 'dept3'

    返回:
    - 按指定维度分组的工时统计数据列表
    """
    # 验证维度参数
    valid_dimensions = ['affiliation', 'dept1', 'dept2', 'dept3']
    for dim in dimensions:
        if dim not in valid_dimensions:
            raise ValueError(f"无效的统计维度: {dim}，有效维度为: {valid_dimensions}")

    # 如果没有指定维度，返回空列表
    if not dimensions:
        return []

    # 构建SQL查询
    select_clause = ", ".join([f"e.{dim} as {dim}" for dim in dimensions])
    group_by_clause = ", ".join([f"e.{dim}" for dim in dimensions])
    order_by_clause = ", ".join([f"e.{dim}" for dim in dimensions])

    query = f"""
        SELECT
            {select_clause},
            SUM(ar.day_total_work_hours) as day_total_hours,
            SUM(ar.night_work_hours) as night_hours,
            SUM(ar.overtime_hours) as overtime_hours,
            COUNT(ar.record_id) as record_count
        FROM
            attendance_records ar
        JOIN
            employees e ON ar.employee_id = e.employee_id
        WHERE
            ar.date BETWEEN ? AND ?
        GROUP BY
            {group_by_clause}
        ORDER BY
            {order_by_clause}
    """

    # 执行查询
    with get_db() as conn:
        cursor = conn.cursor()
        cursor.execute(query, (start_date, end_date))

        columns = [description[0] for description in cursor.description]
        results = [dict(zip(columns, row)) for row in cursor.fetchall()]

        # 计算总计数据
        total_day_hours = sum(row['day_total_hours'] for row in results)
        total_night_hours = sum(row['night_hours'] for row in results)
        total_overtime_hours = sum(row['overtime_hours'] for row in results)
        total_records = sum(row['record_count'] for row in results)

        # 添加百分比数据
        for row in results:
            if total_day_hours > 0:
                row['day_hours_percentage'] = round((row['day_total_hours'] / total_day_hours) * 100, 2)
            else:
                row['day_hours_percentage'] = 0

            if total_night_hours > 0:
                row['night_hours_percentage'] = round((row['night_hours'] / total_night_hours) * 100, 2)
            else:
                row['night_hours_percentage'] = 0

            if total_overtime_hours > 0:
                row['overtime_hours_percentage'] = round((row['overtime_hours'] / total_overtime_hours) * 100, 2)
            else:
                row['overtime_hours_percentage'] = 0

        return results

def get_employee_workhours_statistics(
        employee_ids: List[str],
        start_date: str,
        end_date: str
) -> List[Dict[str, Any]]:
    """
    获取特定员工在指定日期范围内的工时统计数据

    参数:
    - employee_ids: 员工ID列表或姓名列表，如果为空则查询所有员工
    - start_date: 开始日期
    - end_date: 结束日期

    返回:
    - 员工工时统计数据列表，包含每个员工的汇总数据和每日详细记录
    """
    # 如果employee_ids为空，则查询所有员工
    if not employee_ids:
        logger.info(f"未指定员工ID，将查询所有员工的工时统计: 日期范围={start_date}至{end_date}")
        
    logger.info(f"获取员工工时统计: 员工IDs={employee_ids}, 日期范围={start_date}至{end_date}")

    # 首先尝试查找员工信息，可能通过工号或姓名
    with get_db() as conn:
        cursor = conn.cursor()
        
        actual_employee_ids = []
        
        # 如果employee_ids为空，获取所有员工ID
        if not employee_ids:
            query = "SELECT employee_id FROM employees"
            cursor.execute(query)
            actual_employee_ids = [row[0] for row in cursor.fetchall()]
            logger.info(f"获取到所有员工ID: {len(actual_employee_ids)} 个")
        else:
            # 先通过姓名查找对应的工号
            for emp_id in employee_ids:
                # 检查是否为工号
                query = "SELECT employee_id FROM employees WHERE employee_id = ?"
                cursor.execute(query, (emp_id,))
                result = cursor.fetchone()
                if result:
                    actual_employee_ids.append(result[0])
                    logger.info(f"找到员工工号: {emp_id}")
                else:
                    # 如果不是工号，尝试通过姓名查找
                    query = "SELECT employee_id FROM employees WHERE name = ?"
                    cursor.execute(query, (emp_id,))
                    result = cursor.fetchone()
                    if result:
                        actual_employee_ids.append(result[0])
                        logger.info(f"通过姓名 '{emp_id}' 找到员工工号: {result[0]}")
                    else:
                        logger.warning(f"未找到员工: {emp_id}")
            
            if not actual_employee_ids:
                logger.warning("未找到任何有效的员工ID")
                return []
            
        logger.info(f"实际查询的员工工号: {actual_employee_ids}")
        
        # 构建查询参数，用于IN子句
        placeholders = ','.join(['?'] * len(actual_employee_ids))
        
        # 查询员工基本信息
        employee_query = f"""
            SELECT 
                employee_id, 
                name, 
                affiliation, 
                dept1, 
                dept2, 
                dept3, 
                position
            FROM 
                employees
            WHERE 
                employee_id IN ({placeholders})
        """
        
        # 查询员工基本信息
        cursor.execute(employee_query, actual_employee_ids)
        employees = {row[0]: dict(zip(['employee_id', 'name', 'affiliation', 'dept1', 'dept2', 'dept3', 'position'], row)) 
                    for row in cursor.fetchall()}
        
        logger.info(f"找到 {len(employees)} 个员工的基本信息")
        
        # 查询员工考勤汇总数据
        summary_query = f"""
            SELECT 
                ar.employee_id,
                COUNT(DISTINCT ar.date) as total_work_days,
                SUM(ar.day_total_work_hours) as day_total_work_hours,
                SUM(ar.night_work_hours) as night_work_hours,
                SUM(ar.overtime_hours) as overtime_hours
            FROM 
                attendance_records ar
            WHERE 
                ar.employee_id IN ({placeholders})
                AND ar.date BETWEEN ? AND ?
            GROUP BY 
                ar.employee_id
        """
        
        # 查询汇总数据
        cursor.execute(summary_query, actual_employee_ids + [start_date, end_date])
        summary_results = cursor.fetchall()
        logger.info(f"找到 {len(summary_results)} 条汇总数据记录")
        
        summary_columns = ['employee_id', 'total_work_days', 'day_total_work_hours', 'night_work_hours', 'overtime_hours']
        for row in summary_results:
            employee_id = row[0]
            if employee_id in employees:
                summary_data = dict(zip(summary_columns, row))
                employees[employee_id].update(summary_data)
                # 初始化加班详情字典
                employees[employee_id]['overtime_details'] = {}
            else:
                logger.warning(f"找到汇总数据的员工ID {employee_id} 不在员工基本信息中")
        
        # 查询加班类型详情
        overtime_details_query = f"""
            SELECT 
                ar.employee_id,
                ar.overtime_type,
                SUM(ar.overtime_hours) as overtime_hours
            FROM 
                attendance_records ar
            WHERE 
                ar.employee_id IN ({placeholders})
                AND ar.date BETWEEN ? AND ?
                AND ar.overtime_hours > 0
            GROUP BY 
                ar.employee_id, ar.overtime_type
        """
        
        cursor.execute(overtime_details_query, actual_employee_ids + [start_date, end_date])
        overtime_results = cursor.fetchall()
        logger.info(f"找到 {len(overtime_results)} 条加班详情记录")
        
        for row in overtime_results:
            employee_id, overtime_type, overtime_hours = row
            if employee_id in employees:
                # 使用加班类型作为键，加班工时作为值
                employees[employee_id]['overtime_details'][str(overtime_type)] = overtime_hours
        
        # 查询每日详情
        daily_records_query = f"""
            SELECT 
                ar.employee_id,
                ar.date,
                ar.weekday,
                ar.day_total_work_hours,
                ar.night_work_hours,
                ar.overtime_hours,
                ar.overtime_type,
                at.type_name as attendance_type
            FROM 
                attendance_records ar
            LEFT JOIN
                attendance_types at ON ar.attendance_type_id = at.type_id
            WHERE 
                ar.employee_id IN ({placeholders})
                AND ar.date BETWEEN ? AND ?
            ORDER BY 
                ar.employee_id, ar.date
        """
        
        cursor.execute(daily_records_query, actual_employee_ids + [start_date, end_date])
        daily_results = cursor.fetchall()
        logger.info(f"找到 {len(daily_results)} 条每日详情记录")
        
        daily_columns = ['employee_id', 'date', 'weekday', 'day_total_work_hours', 
                         'night_work_hours', 'overtime_hours', 'overtime_type', 'attendance_type']
        
        # 为每个员工初始化每日记录列表
        for employee_id in employees:
            employees[employee_id]['daily_records'] = []
        
        # 填充每日记录
        for row in daily_results:
            daily_data = dict(zip(daily_columns, row))
            employee_id = daily_data.pop('employee_id')  # 从每日数据中移除employee_id
            if employee_id in employees:
                # 格式化日期为字符串
                daily_data['date'] = daily_data['date']
                employees[employee_id]['daily_records'].append(daily_data)
        
        # 确保所有员工都有基本的统计字段，即使没有考勤记录
        for employee_id, employee_data in employees.items():
            if 'total_work_days' not in employee_data:
                employee_data.update({
                    'total_work_days': 0,
                    'day_total_work_hours': 0,
                    'night_work_hours': 0,
                    'overtime_hours': 0,
                    'overtime_details': {},
                    'daily_records': []
                })
        
        result = list(employees.values())
        logger.info(f"返回 {len(result)} 个员工的统计数据")
        return result